import os
import re
from datetime import datetime

from flask import render_template, request, jsonify
from sqlalchemy import text
from werkzeug.utils import secure_filename

from tzq.aaa import aaa_bp
from tzq.extensions import db


# 分析SQL文件
def analyze_sql_file(file_path, session_id):
    try:
        with open(file_path, 'r', encoding='utf-8') as f:
            content = f.read()

        # 获取文件大小
        file_size = os.path.getsize(file_path)

        # 1. 提取表名 (CREATE TABLE)
        table_names = list(
            set(re.findall(r'CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?([\w\.]+)', content, re.IGNORECASE)))

        # 2. 提取索引名 (CREATE INDEX)
        index_names = list(set(re.findall(r'CREATE\s+(?:UNIQUE\s+)?INDEX\s+(?:IF\s+NOT\s+EXISTS\s+)?([\w\.]+)', content,
                                          re.IGNORECASE)))

        # 3. 检查UPDATE是否更新last_update_date字段
        updates_last_update = bool(re.search(
            r'UPDATE\s+[\w\.]+\s+SET\s+.*(last_update_date|updated_at)\s*=',
            content,
            re.IGNORECASE
        ))

        # 4. 检查UPDATE是否有WHERE条件
        update_has_where = True
        update_statements = re.findall(r'UPDATE\s+[\w\.]+\s+SET\s+.*?(?:WHERE\s+.*?|$)', content,
                                       re.IGNORECASE | re.DOTALL)
        for statement in update_statements:
            if not re.search(r'WHERE\s+.+', statement, re.IGNORECASE):
                update_has_where = False
                break

        # 5. 检查DELETE是否有WHERE条件
        delete_has_where = True
        delete_statements = re.findall(r'DELETE\s+FROM\s+[\w\.]+.*?(?:WHERE\s+.*?|$)', content,
                                       re.IGNORECASE | re.DOTALL)
        for statement in delete_statements:
            if not re.search(r'WHERE\s+.+', statement, re.IGNORECASE):
                delete_has_where = False
                break

        # 6. 检查中文分号
        has_chinese_semicolon = '；' in content

        # 7. 检查前5行是否有search_path
        has_search_path = any('set search_path' in line.lower() for line in content.splitlines()[:5])

        # 保存分析结果到数据库

        v_sql = text('''INSERT INTO file_analysis (session_id, file_name, file_size, table_names, index_names,
                                                   updates_last_update, update_has_where, delete_has_where,
                                                   has_chinese_semicolon, has_search_path, file_path)
                        VALUES (:session_id, :file_name, :file_size, :table_names, :index_names, :updates_last_update,
                                :update_has_where,
                                :delete_has_where, :has_chinese_semicolon, :has_search_path, :file_path)
                     ''')
        db.session.execute(v_sql, {
            'session_id': session_id,
            'file_name': os.path.basename(file_path),
            'file_size': file_size,
            'table_names': table_names,
            'index_names': index_names,
            'updates_last_update': updates_last_update,
            'update_has_where': update_has_where,
            'delete_has_where': delete_has_where,
            'has_chinese_semicolon': has_chinese_semicolon,
            'has_search_path': has_search_path,
            'file_path': file_path
        })
        db.session.commit()
        # return jsonify({"code": 1, "msg": "【OK】"})
        return True
    except Exception as e:
        print(f"Error analyzing file {file_path}: {str(e)}")
        return False


# Flask路由
@aaa_bp.route('/')
def index():
    return render_template('index.html')


@aaa_bp.route('/upload', methods=['POST'])
def upload_folder():
    if 'folder' not in request.files:
        return jsonify({'error': 'No folder uploaded'}), 400

    folder = request.files.getlist('folder')
    if not folder:
        return jsonify({'error': 'No files in folder'}), 400

    # 创建会话ID
    session_id = datetime.now().strftime('%Y%m%d%H%M%S%f')
    folder_name = secure_filename(request.form.get('folder_name', 'unknown'))

    # 保存会话信息
    db.session.execute('''
                       INSERT INTO analysis_sessions (session_id, folder_name)
                       VALUES (%s, %s)
                       ''', (session_id, folder_name))
    db.session.commit()

    # 处理上传的文件
    for file in folder:
        if file.filename == '':
            continue

        filename = secure_filename(file.filename)
        file_path = os.path.join(app.config['UPLOAD_FOLDER'], session_id, filename)
        os.makedirs(os.path.dirname(file_path), exist_ok=True)
        file.save(file_path)

        # 分析SQL文件
        if filename.lower().endswith('.sql'):
            analyze_sql_file(file_path, session_id)

    return jsonify({
        'success': True,
        'session_id': session_id,
        'message': f'{len(folder)} files uploaded and analyzed'
    })


@aaa_bp.route('/results/<session_id>')
def get_results(session_id):
    # 获取会话信息
    session = db.session.execute('SELECT * FROM analysis_sessions WHERE session_id = %s', (session_id,)).fetchone()

    if not session:
        return jsonify({'error': 'Session not found'}), 404

    # 获取分析结果
    results = db.session.execute('SELECT * FROM file_analysis WHERE session_id = %s', (session_id,)).fetchall()
    results = cur.fetchall()

    # 转换为字典列表
    columns = [desc[0] for desc in cur.description]
    results = [dict(zip(columns, row)) for row in results]

    cur.close()
    conn.close()

    # 格式化结果
    for result in results:
        # 转换文件大小为可读格式
        size = result['file_size']
        if size < 1024:
            result['file_size'] = f"{size} B"
        elif size < 1024 * 1024:
            result['file_size'] = f"{size / 1024:.2f} KB"
        else:
            result['file_size'] = f"{size / (1024 * 1024):.2f} MB"

        # 处理布尔值显示
        result['updates_last_update'] = '✅' if result['updates_last_update'] else '❌'
        result['update_has_where'] = '✅' if result['update_has_where'] else '❌'
        result['delete_has_where'] = '✅' if result['delete_has_where'] else '❌'
        result['has_chinese_semicolon'] = '⚠️' if result['has_chinese_semicolon'] else '✅'
        result['has_search_path'] = '✅' if result['has_search_path'] else '❌'

    return jsonify(results)


@aaa_bp.route('/view_file/<path:file_path>')
def view_file(file_path):
    # 安全验证，防止路径遍历攻击
    if '..' in file_path or file_path.startswith('/'):
        return "Invalid file path", 400

    full_path = os.path.join(app.config['UPLOAD_FOLDER'], file_path)
    if not os.path.exists(full_path):
        return "File not found", 404

    with open(full_path, 'r', encoding='utf-8', errors='ignore') as f:
        content = f.read()

    # 高亮SQL关键字
    sql_keywords = [
        'SELECT', 'FROM', 'WHERE', 'INSERT', 'INTO', 'VALUES', 'UPDATE', 'DELETE',
        'CREATE', 'TABLE', 'INDEX', 'ALTER', 'DROP', 'SET', 'JOIN', 'ON', 'AND', 'OR',
        'GROUP BY', 'ORDER BY', 'HAVING', 'LIMIT', 'OFFSET', 'AS', 'DISTINCT',
        'UNION', 'ALL', 'CASE', 'WHEN', 'THEN', 'ELSE', 'END', 'EXISTS', 'NOT',
        'NULL', 'PRIMARY KEY', 'FOREIGN KEY', 'REFERENCES', 'DEFAULT', 'UNIQUE',
        'CHECK', 'CONSTRAINT', 'VIEW', 'SEQUENCE', 'TRIGGER', 'FUNCTION', 'PROCEDURE',
        'BEGIN', 'COMMIT', 'ROLLBACK', 'SAVEPOINT', 'EXPLAIN', 'VACUUM', 'ANALYZE',
        'GRANT', 'REVOKE', 'TO', 'PUBLIC', 'CASCADE', 'RESTRICT'
    ]

    for keyword in sql_keywords:
        content = re.sub(
            rf'\b{keyword}\b',
            f'<span class="sql-keyword">{keyword}</span>',
            content,
            flags=re.IGNORECASE
        )

    # 添加行号
    lines = content.split('\n')
    numbered_lines = []
    for i, line in enumerate(lines, 1):
        numbered_lines.append(f'<div class="line"><span class="line-number">{i:4d}</span> {line}</div>')

    content_with_line_numbers = '\n'.join(numbered_lines)

    return render_template('file_content.html', content=content_with_line_numbers)
